IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tr_ProjectSummaryForUser_Get]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[tr_ProjectSummaryForUser_Get]
GO

CREATE PROCEDURE [dbo].[tr_ProjectSummaryForUser_Get]
	@UserId UNIQUEIDENTIFIER,
	@ProjectId UNIQUEIDENTIFIER = NULL,
	@ActiveInd BIT = NULL
AS

SELECT 
	p.ProjectId,
	p.Name,
	p.Description,
	p.ActiveInd,
	f.EmailId,
	f.EntryDt,
	e.EmailAddress,
	(SELECT Count(DISTINCT(tr_Email.EmailId) )
		FROM tr_Email INNER JOIN tr_File ON (tr_Email.EmailId = tr_File.EmailId)
		WHERE ProjectId = p.ProjectId) as EmailCount,
	ps.ProjectEmail
FROM
	tr_Project p
	INNER JOIN tr_ProjectUser pu ON p.ProjectId = pu.ProjectId
	INNER JOIN tr_ProjectSettings ps ON p.ProjectId = ps.ProjectId
	LEFT JOIN tr_File f ON f.ProjectId = p.ProjectId 
		AND f.FileId = (SELECT TOP 1 fm.FileId FROM tr_File fm WHERE fm.ProjectId = p.ProjectId ORDER BY fm.EntryDt DESC)
	LEFT JOIN tr_Email e ON e.EmailId = f.EmailId
		
WHERE
	pu.UserId = @UserId
	--AND (f.DeletedInd = 0 OR f.DeletedInd IS NULL)
	--AND (f.ArchivedInd = 0 OR f.DeletedInd IS NULL)
	AND (p.DeletedInd = 0)
	AND (p.ProjectId = @ProjectId OR @ProjectId IS NULL)
	AND (p.ActiveInd = @ActiveInd OR @ActiveInd IS NULL)
ORDER BY
	p.Name
GO


